using System;
using System.Data;
using System.Data.OracleClient;
using System.Runtime.CompilerServices;

namespace XEAS.Framework.Data
{
    public class OracleAccessLayer : iDataAccessLayer
    {
        private OracleConnection _commonConnection;
        private string _connectionString;
        public bool AutoCloseConnection { get; set; }
        public bool UseCommonConnection { get; set; }

        public OracleAccessLayer(string connectionString)
        {
            AutoCloseConnection = true;
            _commonConnection = null;
            _connectionString = string.Empty;
            UseCommonConnection = true;
            _connectionString = connectionString;
            _commonConnection = new OracleConnection(_connectionString);
            _commonConnection.Open();
            _commonConnection.Close();
        }

        public OracleAccessLayer(string connectionString, bool autoCloseConnection)
        {
            AutoCloseConnection = true;
            _commonConnection = null;
            _connectionString = string.Empty;
            UseCommonConnection = true;
            _connectionString = connectionString;
            AutoCloseConnection = autoCloseConnection;
            _commonConnection = new OracleConnection(_connectionString);
            _commonConnection.Open();
            _commonConnection.Close();
        }

        public OracleAccessLayer(string connectionString, bool autoCloseConnection, bool useCommonConnection)
        {
            AutoCloseConnection = true;
            _commonConnection = null;
            _connectionString = string.Empty;
            UseCommonConnection = true;
            _connectionString = connectionString;
            AutoCloseConnection = autoCloseConnection;
            UseCommonConnection = useCommonConnection;
            _commonConnection = new OracleConnection(_connectionString);
            _commonConnection.Open();
            _commonConnection.Close();
        }

        #region iDataAccessLayer Members

        public DataSet Execute(IDbCommand query, eExecutionType executionType)
        {
            OracleDataAdapter adapter;
            if (query == null)
            {
                throw new Exception("G&L iCore: 'query' object cannot be null.");
            }
            var dataSet = new DataSet();
            var selectCommand = query as OracleCommand;
            switch (executionType)
            {
                case eExecutionType.StoredProcedure:
                    selectCommand.CommandType = CommandType.StoredProcedure;
                    break;

                case eExecutionType.TextCommand:
                    selectCommand.CommandType = CommandType.Text;
                    break;
            }
            if (UseCommonConnection)
            {
                ValidateConnection();
                selectCommand.Connection = _commonConnection;
                adapter = new OracleDataAdapter(selectCommand);
                adapter.Fill(dataSet);
                if (AutoCloseConnection)
                {
                    _commonConnection.Close();
                }
            }
            else
            {
                var connection = new OracleConnection(ConnectionString);
                var adapter2 = new OracleDataAdapter(selectCommand);
                adapter2.SelectCommand.Connection = connection;
                adapter = adapter2;
                adapter.Fill(dataSet);
                connection.Close();
                connection.Dispose();
            }
            adapter.Dispose();
            selectCommand.Dispose();
            return dataSet;
        }

        public DataSet Execute(string query, eExecutionType executionType)
        {
            return this.Execute(new OracleCommand(query), executionType);
        }

        public DataSet Execute(string query, IDataParameter[] parameters, eExecutionType executionType)
        {
            var command = new OracleCommand(query);
            for (int i = 0; i <= (parameters.Length - 1); i++)
            {
                command.Parameters.Add(parameters[i] as OracleParameter);
            }
            return this.Execute(command, executionType);
        }

        public DataSet Execute(string query, string[] parametersName, object[] values, eExecutionType executionType)
        {
            var command = new OracleCommand(query);
            for (int i = 0; i <= (parametersName.Length - 1); i++)
            {
                command.Parameters.AddWithValue(parametersName[i], values[i]);
            }
            return this.Execute(command, executionType);
        }

        public int ExecuteNonQuery(IDbCommand query, eExecutionType executionType)
        {
            int num;
            if (query == null)
            {
                throw new Exception("G&L iCore: 'query' object cannot be null.");
            }
            var command = query as OracleCommand;
            switch (executionType)
            {
                case eExecutionType.StoredProcedure:
                    command.CommandType = CommandType.StoredProcedure;
                    break;

                case eExecutionType.TextCommand:
                    command.CommandType = CommandType.Text;
                    break;
            }
            if (UseCommonConnection)
            {
                ValidateConnection();
                command.Connection = _commonConnection;
                num = command.ExecuteNonQuery();
                if (AutoCloseConnection)
                {
                    _commonConnection.Close();
                }
            }
            else
            {
                var connection = new OracleConnection(ConnectionString);
                connection.Open();
                command.Connection = connection;
                num = command.ExecuteNonQuery();
                connection.Close();
                connection.Dispose();
            }
            command.Dispose();
            return num;
        }

        public int ExecuteNonQuery(string query, eExecutionType executionType)
        {
            return this.ExecuteNonQuery(new OracleCommand(query), executionType);
        }

        public int ExecuteNonQuery(string query, IDataParameter[] parameters, eExecutionType executionType)
        {
            var command = new OracleCommand(query);
            for (int i = 0; i <= (parameters.Length - 1); i++)
            {
                command.Parameters.Add(parameters[i] as OracleParameter);
            }
            return this.ExecuteNonQuery(command, executionType);
        }

        public int ExecuteNonQuery(string query, string[] parametersName, object[] values, eExecutionType executionType)
        {
            if (parametersName.Length != values.Length)
            {
                throw new Exception("G&L iCore: ParameterName's Length must be equals with Values' Length.");
            }
            var command = new OracleCommand(query);
            for (int i = 0; i <= (parametersName.Length - 1); i++)
            {
                command.Parameters.AddWithValue(parametersName[i], values[i]);
            }
            return this.ExecuteNonQuery(command, executionType);
        }

        public IDataReader ExecuteReader(IDbCommand query, eExecutionType executionType)
        {
            OracleDataReader reader;
            if (query == null)
            {
                throw new Exception("G&L iCore: 'query' object cannot be null.");
            }
            var command = query as OracleCommand;
            switch (executionType)
            {
                case eExecutionType.StoredProcedure:
                    command.CommandType = CommandType.StoredProcedure;
                    break;

                case eExecutionType.TextCommand:
                    command.CommandType = CommandType.Text;
                    break;
            }
            if (UseCommonConnection)
            {
                ValidateConnection();
                command.Connection = _commonConnection;
                reader = command.ExecuteReader();
                if (AutoCloseConnection)
                {
                    _commonConnection.Close();
                }
            }
            else
            {
                var connection = new OracleConnection(ConnectionString);
                connection.Open();
                command.Connection = connection;
                reader = command.ExecuteReader();
                connection.Close();
                connection.Dispose();
            }
            command.Dispose();
            return reader;
        }

        public IDataReader ExecuteReader(string query, eExecutionType executionType)
        {
            return this.ExecuteReader(new OracleCommand(query), executionType);
        }

        public IDataReader ExecuteReader(IDbCommand query, CommandBehavior commandBehaviour,
                                         eExecutionType executionType)
        {
            OracleDataReader reader;
            if (query == null)
            {
                throw new Exception("G&L iCore: 'query' object cannot be null.");
            }
            var command = query as OracleCommand;
            switch (executionType)
            {
                case eExecutionType.StoredProcedure:
                    command.CommandType = CommandType.StoredProcedure;
                    break;

                case eExecutionType.TextCommand:
                    command.CommandType = CommandType.Text;
                    break;
            }
            if (UseCommonConnection)
            {
                ValidateConnection();
                command.Connection = _commonConnection;
                reader = command.ExecuteReader(commandBehaviour);
                if (AutoCloseConnection)
                {
                    _commonConnection.Close();
                }
            }
            else
            {
                var connection = new OracleConnection(ConnectionString);
                connection.Open();
                command.Connection = connection;
                reader = command.ExecuteReader(commandBehaviour);
                connection.Close();
                connection.Dispose();
            }
            command.Dispose();
            return reader;
        }

        public IDataReader ExecuteReader(string query, CommandBehavior commandBehaviour, eExecutionType executionType)
        {
            return this.ExecuteReader(new OracleCommand(query), commandBehaviour, executionType);
        }

        public IDataReader ExecuteReader(string query, IDataParameter[] parameters, eExecutionType executionType)
        {
            var command = new OracleCommand(query);
            for (int i = 0; i <= (parameters.Length - 1); i++)
            {
                command.Parameters.Add(parameters[i] as OracleParameter);
            }
            return this.ExecuteReader(command, executionType);
        }

        public IDataReader ExecuteReader(string query, IDataParameter[] parameters, CommandBehavior commandBehaviour,
                                         eExecutionType executionType)
        {
            var command = new OracleCommand(query);
            for (int i = 0; i <= (parameters.Length - 1); i++)
            {
                command.Parameters.Add(parameters[i] as OracleParameter);
            }
            return this.ExecuteReader(command, commandBehaviour, executionType);
        }

        public IDataReader ExecuteReader(string query, string[] parametersName, object[] values,
                                         eExecutionType executionType)
        {
            if (parametersName.Length != values.Length)
            {
                throw new Exception("G&L iCore: ParameterName's Length must be equals with Values' Length.");
            }
            var command = new OracleCommand(query);
            for (int i = 0; i <= (parametersName.Length - 1); i++)
            {
                command.Parameters.AddWithValue(parametersName[i], values[i]);
            }
            return this.ExecuteReader(command, executionType);
        }

        public IDataReader ExecuteReader(string query, string[] parametersName, object[] values,
                                         CommandBehavior commandBehaviour, eExecutionType executionType)
        {
            if (parametersName.Length != values.Length)
            {
                throw new Exception("G&L iCore: ParameterName's Length must be equals with Values' Length.");
            }
            var command = new OracleCommand(query);
            for (int i = 0; i <= (parametersName.Length - 1); i++)
            {
                command.Parameters.AddWithValue(parametersName[i], values[i]);
            }
            return this.ExecuteReader(command, commandBehaviour, executionType);
        }

        public object ExecuteScalar(IDbCommand query, eExecutionType executionType)
        {
            object obj2;
            if (query == null)
            {
                throw new Exception("G&L iCore: 'query' object cannot be null.");
            }
            var command = query as OracleCommand;
            switch (executionType)
            {
                case eExecutionType.StoredProcedure:
                    command.CommandType = CommandType.StoredProcedure;
                    break;

                case eExecutionType.TextCommand:
                    command.CommandType = CommandType.Text;
                    break;
            }
            if (UseCommonConnection)
            {
                ValidateConnection();
                command.Connection = _commonConnection;
                obj2 = command.ExecuteScalar();
                if (AutoCloseConnection)
                {
                    _commonConnection.Close();
                }
            }
            else
            {
                var connection = new OracleConnection(ConnectionString);
                connection.Open();
                command.Connection = connection;
                obj2 = command.ExecuteScalar();
                connection.Close();
            }
            command.Dispose();
            return obj2;
        }

        public object ExecuteScalar(string query, eExecutionType executionType)
        {
            return this.ExecuteScalar(new OracleCommand(query), executionType);
        }

        public object ExecuteScalar(string query, IDataParameter[] parameters, eExecutionType executionType)
        {
            var command = new OracleCommand(query);
            for (int i = 0; i <= (parameters.Length - 1); i++)
            {
                command.Parameters.Add(parameters[i] as OracleParameter);
            }
            return this.ExecuteScalar(command, executionType);
        }

        public object ExecuteScalar(string query, string[] parametersName, object[] values, eExecutionType executionType)
        {
            if (parametersName.Length != values.Length)
            {
                throw new Exception("G&L iCore: ParameterName's Length must be equals with Values' Length.");
            }
            var command = new OracleCommand(query);
            for (int i = 0; i <= (parametersName.Length - 1); i++)
            {
                command.Parameters.AddWithValue(parametersName[i], values[i]);
            }
            return this.ExecuteScalar(command, executionType);
        }

       
        public IDbConnection Connection
        {
            get { return _commonConnection; }
            set
            {
                _commonConnection = value as OracleConnection;
                _commonConnection.Open();
                _commonConnection.Close();
            }
        }

        public string ConnectionString
        {
            get { return _connectionString; }
            set
            {
                _connectionString = value;
                _commonConnection = new OracleConnection(_connectionString);
                _commonConnection.Open();
                _commonConnection.Close();
            }
        }

        #endregion

        private void ValidateConnection()
        {
            if (UseCommonConnection && (_commonConnection.State != ConnectionState.Open))
            {
                _commonConnection.Open();
            }
        }
    }
}